﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Controls;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using ProjectWizardManagementv1.WpfComponents.ComponentClasses;

namespace ProjectWizardManagementv1.Infrastructure.Concrete
{
    public class ExcelDocumentParser
    {
        private static readonly Regex _gridPattern = new Regex("([A-Z]+)([0-9]+)");
        private const int COLUMN_GROUP = 1;
        private const int ROW_GROUP = 2;
        private const int LETTER_A_DIFFERENCE = 'A';

        public CellTextField[,] ParseDocument(string fileName)
        {
            CellTextField[,] grid;
            using(SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
            {
                var theSheet = document.WorkbookPart.Workbook.Descendants<Sheet>().FirstOrDefault();

                var sheet = ((WorksheetPart) (document.WorkbookPart.GetPartById(theSheet.Id))).Worksheet;

                    var dimension = sheet.SheetDimension;
                    var reference = dimension.Reference;
                    grid = CreateGrid(reference);
                var rows = sheet.Descendants<Row>().ToList();
                    for (int i = 0; i < rows.Count(); i++)
                    {
                        var currentRow = rows[i].Descendants<Cell>().ToList();
                        for (int j = 0; j < currentRow.Count(); j++)
                        {
                            var cellRef = currentRow[j].CellReference;
                            var column = _gridPattern.Match(cellRef).Groups[COLUMN_GROUP].Value.ToCharArray().FromCharToInt();
                            var row = int.Parse(_gridPattern.Match(cellRef).Groups[ROW_GROUP].Value) - 1;
                            grid[row, column].OriginalText = ExcelExtensions.GetSharedStringItemById(currentRow[j].CellValue, document.WorkbookPart.SharedStringTablePart);
                        }
                    }
            }

            return grid;
        }

        private CellTextField[,] CreateGrid(StringValue reference)
        {
            var dimensions = reference.ToString().Split(':');
            var maxDimension = dimensions[1];

            var columnDimension = _gridPattern.Match(maxDimension).Groups[COLUMN_GROUP].Value;
            var rowDimension = _gridPattern.Match(maxDimension).Groups[ROW_GROUP].Value;

            var rowDimensionNumber = int.Parse(rowDimension);
            var columnDimensionNumber = columnDimension.ToCharArray().FromCharToInt() + 1;
            var grid =  new CellTextField[rowDimensionNumber,columnDimensionNumber];
            for(int i = 0; i < rowDimensionNumber; i++)
            {
                for(int j = 0; j < columnDimensionNumber; j++)
                {
                    grid[i, j] = new CellTextField 
                        {Reference = j.FromIntToExcelChar() + (i + 1), OriginalText = string.Empty};
                }
            }

            return grid;
        }
    }

    public static class ExcelExtensions
    {
        private const int LETTER_A_DIFFERENCE = 'A';

        public static int FromCharToInt(this char[] characters)
        {
            if (characters.Count() == 1)
                return characters[0] - LETTER_A_DIFFERENCE;
            else
            {
                return ((characters[0] -LETTER_A_DIFFERENCE + 1)*26) + (characters[1] -LETTER_A_DIFFERENCE);
            }
        }

        public static string FromIntToExcelChar(this int column)
        {
            if (column < 26)
                return ((char)(column + LETTER_A_DIFFERENCE)).ToString(CultureInfo.InvariantCulture);
            else
            {
                var sb = new StringBuilder();
                while(column > 26)
                {
                    int value = column/26;
                    sb.Append((char) (value + LETTER_A_DIFFERENCE));
                    column -= value*26;
                }
                sb.Append((char) (column + LETTER_A_DIFFERENCE));
                return sb.ToString();
            }
        }

        public static string GetSharedStringItemById(CellValue id, SharedStringTablePart stringTable)
        {
            if(id != null)
            {
                int numId;
            
                if (!int.TryParse(id.Text, out numId))
                    return id.Text.ToString(CultureInfo.InvariantCulture);

                if (numId < stringTable.SharedStringTable.Count && numId >= 0)
                    return stringTable.SharedStringTable.Elements<SharedStringItem>().ElementAt(numId).InnerText;
                else return numId.ToString(CultureInfo.InvariantCulture);
            }
            return "";
        }
    }
}
